#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
湖州地区数据Excel导出器
按县市分页，每页内按乡镇分组导出数据
"""

import sqlite3
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from typing import Dict, List
import os
from datetime import datetime

class HuzhouExcelExporter:
    """湖州地区Excel导出器"""
    
    def __init__(self, db_path: str = "huzhou_real_data.db"):
        self.db_path = db_path
        self.districts = ["德清县", "长兴县", "安吉县"]
    
    def get_connection(self):
        """获取数据库连接"""
        return sqlite3.connect(self.db_path)
    
    def get_district_data(self, district_name: str) -> Dict:
        """获取指定县市的完整数据"""
        conn = self.get_connection()
        
        # 获取该县市的所有数据，按街道分组
        query = """
        SELECT 
            s.name as street_name,
            c.name as community_name,
            r.name as residential_name,
            r.address as residential_address,
            r.location as residential_location
        FROM streets s
        LEFT JOIN communities c ON s.name = c.street_name AND s.district_name = c.district_name
        LEFT JOIN residential_areas r ON c.name = r.community_name AND c.street_name = r.street_name
        WHERE s.district_name = ?
        ORDER BY s.name, c.name, r.name
        """
        
        df = pd.read_sql_query(query, conn, params=(district_name,))
        conn.close()
        
        # 按街道分组数据
        grouped_data = {}
        for street in df['street_name'].unique():
            if pd.notna(street):
                street_data = df[df['street_name'] == street].copy()
                grouped_data[street] = street_data
        
        return grouped_data
    
    def create_styled_workbook(self) -> Workbook:
        """创建带样式的工作簿"""
        wb = Workbook()
        # 删除默认工作表
        wb.remove(wb.active)
        return wb
    
    def add_district_sheet(self, wb: Workbook, district_name: str, district_data: Dict):
        """为县市添加工作表"""
        ws = wb.create_sheet(title=district_name)
        
        # 设置标题样式
        title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
        title_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
        title_alignment = Alignment(horizontal='center', vertical='center')
        
        # 设置表头样式
        header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
        header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
        header_alignment = Alignment(horizontal='center', vertical='center')
        
        # 设置边框
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        current_row = 1
        
        # 添加县市标题
        ws.merge_cells(f'A{current_row}:E{current_row}')
        title_cell = ws[f'A{current_row}']
        title_cell.value = f"{district_name} - 街道社区小区信息"
        title_cell.font = title_font
        title_cell.fill = title_fill
        title_cell.alignment = title_alignment
        title_cell.border = thin_border
        current_row += 2
        
        # 遍历每个街道
        for street_name, street_data in district_data.items():
            # 添加街道标题
            ws.merge_cells(f'A{current_row}:E{current_row}')
            street_cell = ws[f'A{current_row}']
            street_cell.value = f"🏢 {street_name}"
            street_cell.font = Font(name='微软雅黑', size=14, bold=True, color='2F5597')
            street_cell.fill = PatternFill(start_color='DCE6F1', end_color='DCE6F1', fill_type='solid')
            street_cell.alignment = Alignment(horizontal='left', vertical='center')
            street_cell.border = thin_border
            current_row += 1
            
            # 添加表头
            headers = ['社区名称', '小区名称', '小区地址', '位置信息', '备注']
            for col, header in enumerate(headers, 1):
                cell = ws.cell(row=current_row, column=col, value=header)
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = header_alignment
                cell.border = thin_border
            current_row += 1
            
            # 添加数据行
            data_font = Font(name='微软雅黑', size=10)
            data_alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
            
            for _, row in street_data.iterrows():
                # 社区名称
                cell = ws.cell(row=current_row, column=1, value=row['community_name'] if pd.notna(row['community_name']) else '')
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
                
                # 小区名称
                cell = ws.cell(row=current_row, column=2, value=row['residential_name'] if pd.notna(row['residential_name']) else '')
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
                
                # 小区地址
                cell = ws.cell(row=current_row, column=3, value=row['residential_address'] if pd.notna(row['residential_address']) else '')
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
                
                # 位置信息
                cell = ws.cell(row=current_row, column=4, value=row['residential_location'] if pd.notna(row['residential_location']) else '')
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
                
                # 备注
                cell = ws.cell(row=current_row, column=5, value='')
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
                
                current_row += 1
            
            # 添加空行分隔
            current_row += 1
        
        # 设置列宽
        ws.column_dimensions['A'].width = 20  # 社区名称
        ws.column_dimensions['B'].width = 25  # 小区名称
        ws.column_dimensions['C'].width = 35  # 小区地址
        ws.column_dimensions['D'].width = 20  # 位置信息
        ws.column_dimensions['E'].width = 15  # 备注
        
        # 设置行高
        for row in range(1, current_row):
            ws.row_dimensions[row].height = 25
    
    def export_to_excel(self, output_file: str = None) -> str:
        """导出数据到Excel文件"""
        if output_file is None:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            output_file = f"湖州地区数据导出_{timestamp}.xlsx"
        
        # 创建工作簿
        wb = self.create_styled_workbook()
        
        # 为每个县市创建工作表
        for district in self.districts:
            print(f"正在处理 {district} 的数据...")
            district_data = self.get_district_data(district)
            
            if district_data:
                self.add_district_sheet(wb, district, district_data)
                print(f"✅ {district} 数据处理完成")
            else:
                print(f"⚠️ {district} 暂无数据")
        
        # 保存文件
        wb.save(output_file)
        print(f"\n🎉 Excel文件已生成: {output_file}")
        
        return output_file
    
    def get_export_summary(self) -> Dict:
        """获取导出数据摘要"""
        conn = self.get_connection()
        
        summary = {}
        for district in self.districts:
            # 统计各级数据数量
            cursor = conn.cursor()
            
            # 街道数量
            cursor.execute("SELECT COUNT(*) FROM streets WHERE district_name = ?", (district,))
            street_count = cursor.fetchone()[0]
            
            # 社区数量
            cursor.execute("SELECT COUNT(*) FROM communities WHERE district_name = ?", (district,))
            community_count = cursor.fetchone()[0]
            
            # 小区数量
            cursor.execute("SELECT COUNT(*) FROM residential_areas WHERE district_name = ?", (district,))
            residential_count = cursor.fetchone()[0]
            
            summary[district] = {
                '街道数量': street_count,
                '社区数量': community_count,
                '小区数量': residential_count
            }
        
        conn.close()
        return summary


def main():
    """主函数"""
    print("🏘️ 湖州地区数据Excel导出器")
    print("=" * 50)
    
    exporter = HuzhouExcelExporter()
    
    # 显示导出摘要
    summary = exporter.get_export_summary()
    print("\n📊 数据摘要:")
    for district, stats in summary.items():
        print(f"\n{district}:")
        for key, value in stats.items():
            print(f"  {key}: {value}")
    
    print("\n开始导出Excel文件...")
    output_file = exporter.export_to_excel()
    
    print(f"\n✅ 导出完成!")
    print(f"📁 文件位置: {os.path.abspath(output_file)}")
    print("\n📋 文件结构:")
    print("  • 每个县市一个工作表")
    print("  • 每个工作表内按街道分组")
    print("  • 包含社区和小区的详细信息")


if __name__ == "__main__":
    main()